﻿#include "mysql.h"

mysql::mysql()
{
    LastConnectTime=QDateTime::currentDateTime();
}


void mysql::setInformation(QString SQL_ip, QString SQL_Sname, QString Sql_Username, QString SQL_Password,QString connect_name)
{
    this->SQL_ip=SQL_ip;
    this->SQL_Sname=SQL_Sname;
    this->Sql_Username=Sql_Username;
    this->SQL_Password=SQL_Password;
    this->connect_name=connect_name;
}

bool mysql::connect()
{
    db = QSqlDatabase::addDatabase("QMYSQL",connect_name);
    db.setHostName(SQL_ip);//主机名字
    db.setDatabaseName(SQL_Sname);//数据库名字
    db.setUserName(Sql_Username);
    db.setPassword(SQL_Password);
    if (!db.open())
    {

        qDebug()<<"Cannot open database";
        qDebug()<< db.lastError().databaseText();
        return false;
    }
    else
    {
        qDebug()<<connect_name<<"号处理线程连接数据库成功!!";
        query=QSqlQuery(db);
        return true;
    }
}

QString mysql::create_caculate_task(QString task_type ,QString task_data,QString create_id)
{
    LastConnectTime=QDateTime::currentDateTime();
    QString id=rand_id("caculate_task","caculatetask_id");
    QString now_time;
    QDateTime date=QDateTime::currentDateTime();
    now_time=date.toString("yyyy-MM-dd-hh:mm:ss");
    QString create=QString("insert into caculate_task values ('%1', '%2', '%3','%4','%5','%6','%7','%8','%9','%10','N','0.0')").arg(id).arg(task_type).arg("0.0").arg(create_id).arg(now_time).arg(QString()).arg(task_data).arg("").arg("N").arg("");
    query.prepare(create);
    if(!query.exec())
    {
        qDebug()<<"创建计算任务失败!!"<<query.lastError();
        return "";
    }
    else
    {
        qDebug()<<"create_caculate_task success!";
        return id;
    }
}

QString mysql::rand_id(QString table_name, QString idname)
{
    int  rand_id_lenth=10;
    QString tmp = QString("0123456789ABCDEFGHIJKLMNOPQRSTUVWZYZ");
    QString str = QString();
    //QString now_time;
    //QDateTime date=QDateTime::currentDateTime();
    //now_time=date.toString("yyyy-MM-dd-hh:mm:ss");
    QTime t;
    t= QTime::currentTime();
    qsrand(t.msec()+t.second()*1000);
    while(1)
    {
        str.clear();
        for(int i=0;i<rand_id_lenth;i++)
        {
            int ir = qrand()%tmp.length();
            str.append(tmp.at(ir));
        }

        query.prepare(QString("select count(*) from %1 where %2='%3' ").arg(table_name).arg(idname).arg(str));
        int lie;
        query.exec();
        while(query.next())
        {
            lie=query.value(0).toInt();
        }

        if(lie==0)
        {
            return str;
        }
    }
}

bool mysql::create_decomposition_task(QStringList taskid,QString belong_taskid,QStringList task_data,QDateTime createtime)
{

    QTime startTime = QTime::currentTime();
    QString now_time;
    now_time=createtime.toString("yyyy-MM-dd-hh:mm:ss");
    int deal_size=50000;
    int limit=0;
    for(int time=0;time*deal_size<task_data.size();time++)
    {
        qDebug()<<time*deal_size;
        if((time+1)*deal_size<task_data.size())
        {
            limit=(time+1)*deal_size;
        }
        else
        {
            limit=task_data.size();
        }

        QString insertdata=("INSERT INTO  decompose_task values ");
        for(int a=time*deal_size;a<limit;a++)
        {
            if(a%deal_size==0)
            {
                insertdata.append(QString("('%1', '%2', '%3','%4','%5','%6','%7','%8')").arg(taskid[a]).arg(belong_taskid).arg(task_data[a]).arg("").arg(now_time).arg("").arg("").arg("N"));
            }
            else
            {
                insertdata.append(QString(",('%1', '%2', '%3','%4','%5','%6','%7','%8')").arg(taskid[a]).arg(belong_taskid).arg(task_data[a]).arg("").arg(now_time).arg("").arg("").arg("N"));
            }
        }
        insertdata.append(";");
        Common::SQL_writeLock.lock();
        Common::SQL_write.append(insertdata);
        Common::SQL_writeLock.unlock();
        insertdata.clear();

    }

    QTime endTime = QTime::currentTime();
    qDebug() << startTime.msecsTo(endTime);
    LastConnectTime=QDateTime::currentDateTime();
    return true;
}

QString mysql::rand_id(int length)
{
    QString tmp = QString("0123456789ABCDEFGHIJKLMNOPQRSTUVWZYZ");
    QString str = QString();
    QTime t;
    t= QTime::currentTime();
    qsrand(t.msec()+t.second()*1000);
    str.clear();
    for(int i=0;i<length;i++)
    {
        int ir = qrand()%tmp.length();
        str.append(tmp.at(ir));
    }
    return str;
}


QList<QStringList> mysql::Get_unfinshCaculateTask()
{
    QList<QStringList> result;
    if (!query.exec(QString("select * from caculate_task where caculatetask_isfinish='N'")))
    {
        qDebug()<<query.lastError();
        return result;
    }
    else
    {
        while(query.next())
        {
            QStringList data;
            for(int a=0;a<12;a++)
            {
                data<<query.value(a).toString().simplified();
            }
            result.append(data);
        }
    }
    LastConnectTime=QDateTime::currentDateTime();
    return result;
}

QList<QStringList> mysql::Get_DecompositionTask(QString CaculateId)
{
    QList<QStringList> result;

    if (!query.exec(QString("select * from decompose_task where decompose_task_belongid='%1'").arg(CaculateId)))
    {
        qDebug()<<query.lastError();
        return result;
    }
    else
    {
        while(query.next())
        {
            QStringList data;
            for(int a=0;a<8;a++)
            {
                data<<query.value(a).toString().simplified();
            }
            result.append(data);
        }
    }
    LastConnectTime=QDateTime::currentDateTime();
    return result;
}

void mysql::CaculateTask_finish(QString C_taskid, QString C_taskresult, QString endtime, QString timeconsuming)
{
    // qDebug()<<QString("update caculate_task set caculatetask_result='%2',end_time='%3',caculatetask_timeconsuming='%4',caculatetask_isfinish='Y', where caculatetask_id='%1'").arg(C_taskid).arg(C_taskresult).arg(endtime).arg(timeconsuming);
    //    if (!query.exec(QString("update caculate_task set caculatetask_result='%2',end_time='%3',caculatetask_timeconsuming='%4',caculatetask_isfinish='Y',task_schedule='100.0' where caculatetask_id='%1'").arg(C_taskid).arg(C_taskresult).arg(endtime).arg(timeconsuming)))
    //    {
    //        qDebug()<<query.lastError();
    //    }
    Common::SQL_writeLock.lock();
    Common::SQL_write.append(QString("update caculate_task set caculatetask_result='%2',end_time='%3',caculatetask_timeconsuming='%4',caculatetask_isfinish='Y',task_schedule='100.0' where caculatetask_id='%1'").arg(C_taskid).arg(C_taskresult).arg(endtime).arg(timeconsuming));
    Common::SQL_writeLock.unlock();
}


void mysql::Decomposition_taskfinish(QString taskbelongId,QString D_taskid, QString D_taskResult, QString D_taskStarttime, QString D_taskEndtime)
{
    //  qDebug()<<QString("update decompose_task set decompose_task_result='%2',decompose_task_starttime='%3',decompose_task_endtime='%4',decompose_task_isfinish='Y' where decompose_task_id='%1'").arg(D_taskid).arg(D_taskResult).arg(D_taskStarttime).arg(D_taskEndtime);
    //    if (!query.exec(QString("update decompose_task set decompose_task_result='%2',decompose_task_starttime='%3',decompose_task_endtime='%4',decompose_task_isfinish='Y' where decompose_task_belongid='%5' and decompose_task_id='%1'").arg(D_taskid).arg(D_taskResult).arg(D_taskStarttime).arg(D_taskEndtime).arg(taskbelongId)))
    //    {
    //        qDebug()<<query.lastError();
    //    }
    Common::SQL_writeLock.lock();
    Common::SQL_write.append(QString("update decompose_task set decompose_task_result='%2',decompose_task_starttime='%3',decompose_task_endtime='%4',decompose_task_isfinish='Y' where decompose_task_belongid='%5' and decompose_task_id='%1'").arg(D_taskid).arg(D_taskResult).arg(D_taskStarttime).arg(D_taskEndtime).arg(taskbelongId));
    Common::SQL_writeLock.unlock();
}

void mysql::CaculateTask_createDtaskSuccess(QString Caculate_id,int Dtask_size)
{

    //    if (!query.exec(QString("update  caculate_task set create_decomposetask='Y',decomposetask_size='%2' where caculatetask_id='%1'").arg(Caculate_id).arg(QString::number(Dtask_size))))
    //    {
    //        qDebug()<<query.lastError();

    //    }
    Common::SQL_writeLock.lock();
    Common::SQL_write.append(QString("update  caculate_task set create_decomposetask='Y',decomposetask_size='%2' where caculatetask_id='%1'").arg(Caculate_id).arg(QString::number(Dtask_size)));
    Common::SQL_writeLock.unlock();
}


void mysql::CaculateTask_updateschedule(QString CaculateTaskid,double schedule)
{
    //    if(!query.exec(QString("update caculate_task set  task_schedule='%2' where caculatetask_id='%1'").arg(CaculateTaskid).arg(QString::number(schedule))))
    //    {
    //        qDebug()<<query.lastError();
    //    }
    Common::SQL_writeLock.lock();
    Common::SQL_write.append(QString("update caculate_task set  task_schedule='%2' where caculatetask_id='%1'").arg(CaculateTaskid).arg(QString::number(schedule)));
    Common::SQL_writeLock.unlock();
}


QList<QStringList> mysql::GetCaculate_Task(QString data)
{

    QList<QStringList> returndata;
    QString  search;
    if(data.isEmpty())
    {
        search=QString("select * from caculate_task");
    }
    else if(data=="正在运算的任务")
    {
        search=QString("select * from caculate_task where caculatetask_isfinish='N'");
    }
    else
    {
        qDebug()<<"未知查询条件";
        return returndata;
    }
    if(!query.exec(search))
    {
        qDebug()<<query.lastError()<<query.lastError().number();
        return returndata;
    }
    else
    {
        while(query.next())
        {
            QStringList data;
            for(int a=0;a<12;a++)
            {
                data<<query.value(a).toString().simplified();
            }
            returndata.append(data);
        }
    }
    LastConnectTime=QDateTime::currentDateTime();
    return returndata;
}

void mysql::CleanTask()
{
    QStringList data;
    Common::SQL_writeLock.lock();
    data=Common::SQL_write;
    Common::SQL_write.clear();
    Common::SQL_writeLock.unlock();

    if(data.isEmpty())
    {
        return;
    }

    if(!query.exec("BEGIN"))
    {
        qDebug()<<"事务开启失败！";
        return ;
    }
    qDebug()<<"开始清理"<<QDateTime::currentDateTime().toString("hh:mm:ss.zzz ");
    for(int a=0;a<data.size();a++)
    {
        if(!query.exec(data[a]))
        {
            qDebug()<<"语句错误！"<<data[a]<<query.lastError();
            return ;
        }
    }
    qDebug()<<"准备提交事务"<<QDateTime::currentDateTime().toString("hh:mm:ss.zzz ");
    QDateTime now=QDateTime::currentDateTime();
    if(!query.exec("commit"))
    {
        qDebug()<<"事务提交失败";
        return ;
    }
    if(!data.isEmpty())
        qDebug()<<QString("定时执行sql完成,本次执行%1条,耗时:%2,当前时间%3").arg(data.size()).arg(now.msecsTo(QDateTime::currentDateTime())).arg(QDateTime::currentDateTime().toString("hh:mm:ss.zzz "));
    LastConnectTime=QDateTime::currentDateTime();
}



bool mysql::DeleteCaculate_Task(QString Caculate_Taskid)
{
    qDebug()<<"数据库开始删除"<<Caculate_Taskid;
    if(!query.exec("BEGIN"))
    {
        qDebug()<<"事务开启失败！";
        return false;
    }

    if(!query.exec(QString("delete from decompose_task where decompose_task_belongid='%1'").arg(Caculate_Taskid)))
    {
        qDebug()<<query.lastError();
        qDebug()<<"开始回滚!";
        if(!query.exec(("rollback")))
        {
            qDebug()<<query.lastError();
            qDebug()<<"回滚失败!";
            return false;
        }
        else
        {
            qDebug()<<"回滚成功";
            return false;
        }
    }

    if(!query.exec(QString("delete from caculate_task where caculatetask_id='%1'").arg(Caculate_Taskid)))
    {
        qDebug()<<query.lastError();
        qDebug()<<"开始回滚!";
        if(!query.exec(("rollback")))
        {
            qDebug()<<query.lastError();
            qDebug()<<"回滚失败!";
            return false;
        }
        else
        {
            qDebug()<<"回滚成功";
            return false;
        }
    }

    if(!query.exec("commit"))
    {
        qDebug()<<"事务提交失败！";
        qDebug()<<"开始回滚!";
        if(!query.exec(("rollback")))
        {
            qDebug()<<query.lastError();
            qDebug()<<"回滚失败!";
            return false;
        }
        else
        {
            qDebug()<<"回滚成功";
            return false;
        }
        return false;
    }
    LastConnectTime=QDateTime::currentDateTime();
    return true;

}

void mysql::KeepSqlConnect()
{

    if(LastConnectTime.secsTo(QDateTime::currentDateTime())<300)
    {
        return;
    }
    LastConnectTime=QDateTime::currentDateTime();
    if(!query.exec("select 1"))
    {
        db.close();
        db.removeDatabase(connect_name);
        connect();
    }
}
